package com.mishow.db;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import com.mishow.bean.AddrItem;
import com.mishow.bean.Area;

public class DBhelper {
	private SQLiteDatabase db;
	private Context context;
	private DBManager dbm;
	
	public DBhelper(Context context,boolean otherDbName) {
		super();
		this.context = context;
		dbm = new DBManager(context,otherDbName);
	}
	
	public ArrayList<Area> getCity() {
		dbm.openDatabase();
	 	db = dbm.getDatabase();
	 	ArrayList<Area> list = new ArrayList<Area>();
		
	 	try {    
	        String sql = "select * from city";  
	        Cursor cursor = db.rawQuery(sql,null);  
	        cursor.moveToFirst();
	        while (!cursor.isLast()){ 
	        	String code=cursor.getString(cursor.getColumnIndex("code")); 
		        byte bytes[]=cursor.getBlob(2); 
		        String name=new String(bytes,"gbk");
		        Area area=new Area();
		        area.setName(name);
		        area.setCode(code);
		        list.add(area);
		        cursor.moveToNext();
	        }
	        String code=cursor.getString(cursor.getColumnIndex("code")); 
	        byte bytes[]=cursor.getBlob(2); 
	        String name=new String(bytes,"gbk");
	        Area area=new Area();
	        area.setName(name);
	        area.setCode(code);
	        list.add(area);
	        
	    } catch (Exception e) {  
	    	return null;
	    } 
	 	dbm.closeDatabase();
	 	db.close();	
		return list;
		
	}

	public ArrayList<Area> getCity(String pcode) {
		dbm.openDatabase();
		db = dbm.getDatabase();
		ArrayList<Area> list = new ArrayList<Area>();
		
	 	try {    
	        String sql = "select * from city where pcode='"+pcode+"'";  
	        Cursor cursor = db.rawQuery(sql,null);  
	        cursor.moveToFirst();
	        while (!cursor.isLast()){ 
	        	String code=cursor.getString(cursor.getColumnIndex("code")); 
	        	byte bytes[]=cursor.getBlob(2); 
		        String name=new String(bytes,"gbk");
		        Area area=new Area();
		        area.setName(name);
		        area.setCode(code);
		        area.setPcode(pcode);
		        list.add(area);
		        cursor.moveToNext();
	        }
	        String code=cursor.getString(cursor.getColumnIndex("code")); 
	        byte bytes[]=cursor.getBlob(2); 
	        String name=new String(bytes,"gbk");
	        Area area=new Area();
	        area.setName(name);
	        area.setCode(code);
	        area.setPcode(pcode);
	        list.add(area);
	        
	    } catch (Exception e) {  
	    	return null;
	    } 
	 	dbm.closeDatabase();
	 	db.close();	

		return list;

	}
	public ArrayList<Area> getProvince() {
		dbm.openDatabase();
	 	db = dbm.getDatabase();
	 	ArrayList<Area> list = new ArrayList<Area>();
		
	 	try {    
	        String sql = "select * from province";  
	        Cursor cursor = db.rawQuery(sql,null);  
	        cursor.moveToFirst();
	        while (!cursor.isLast()){ 
	        	String code=cursor.getString(cursor.getColumnIndex("code")); 
		        byte bytes[]=cursor.getBlob(2); 
		        String name=new String(bytes,"gbk");
		        Area area=new Area();
		        area.setName(name);
		        area.setCode(code);
		        list.add(area);
		        cursor.moveToNext();
	        }
	        String code=cursor.getString(cursor.getColumnIndex("code")); 
	        byte bytes[]=cursor.getBlob(2); 
	        String name=new String(bytes,"gbk");
	        Area area=new Area();
	        area.setName(name);
	        area.setCode(code);
	        list.add(area);
	        
	    } catch (Exception e) {  
	    	return null;
	    } 
	 	dbm.closeDatabase();
	 	db.close();	
		return list;
		
	}
	public ArrayList<Area> getDistrict(String pcode) {
		dbm.openDatabase();
	 	db = dbm.getDatabase();
	 	ArrayList<Area> list = new ArrayList<Area>();
	 	try {    
	        String sql = "select * from district where pcode='"+pcode+"'";  
	        Cursor cursor = db.rawQuery(sql,null);
	        if (cursor.moveToFirst()) {
				while (!cursor.isLast()) {
					String code = cursor.getString(cursor
							.getColumnIndex("code"));
					byte bytes[] = cursor.getBlob(2);
					String name = new String(bytes, "gbk");
					Area Area = new Area();
					Area.setName(name);
					Area.setPcode(code);
					list.add(Area);
					cursor.moveToNext();
				}
				String code = cursor.getString(cursor.getColumnIndex("code"));
				byte bytes[] = cursor.getBlob(2);
				String name = new String(bytes, "gbk");
				Area Area = new Area();
				Area.setName(name);
				Area.setPcode(code);
				list.add(Area);
			}
	        
	    } catch (Exception e) { 
	    	Log.i("wer", e.toString());
	    } 
	 	dbm.closeDatabase();
	 	db.close();	
		return list;
		
	}
	
	//--------------------------------以下部分以后优化
	private final static byte[] DB_LOCK = new byte[0];
	/**
     * parentId = 0:获取所有省份信息
     * <p>
     * parentId = addr_id:获取某一省份的所有城市信息或某一城市的所有县区信息 <br/>
     *
     * @param [parentId]-[] <br/>
     */
    public ArrayList<AddrItem> getAddrs(int parentId) {
        Cursor cursor = null;
        ArrayList<AddrItem> addrItems = new ArrayList<AddrItem>();
        synchronized (DB_LOCK) {
            try {
            	dbm.openDatabase();
        	 	db = dbm.getDatabase();
                if (db == null) {
                    return null;
                }
                String sql = "select * from ADDR where PARENT_ID = " + parentId;
                cursor = db.rawQuery(sql, null);
                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        AddrItem item = new AddrItem(cursor.getInt(cursor.getColumnIndex("ADDR_ID")),
                                cursor.getInt(cursor.getColumnIndex("ADDR_CODE")),
                                cursor.getString(cursor.getColumnIndex("ADDR_NAME")),
                                cursor.getInt(cursor.getColumnIndex("PARENT_ID")));
                        if (!"市辖区".equals(item.getAddrName())) {
                            addrItems.add(item);
                        }
                    } while (cursor.moveToNext());
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
                if (db != null) {
                    db.close();
                }
            }

        }
        // LogUtil.d("com.njhn.weetmall", "查询数据库：" + addrItems.size() + " "
        // + addrItems.toString());
        return addrItems;
    }

    public ArrayList<AddrItem> getAllCitys() {
        Cursor cursor = null;
        ArrayList<AddrItem> addrItems = new ArrayList<AddrItem>();
        synchronized (DB_LOCK) {
            try {
            	dbm.openDatabase();
        	 	db = dbm.getDatabase();
                if (db == null) {
                    return null;
                }
                String sql = "select * from addr where parent_id in(select addr_id from addr where parent_id=0)";
                cursor = db.rawQuery(sql, null);
                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        AddrItem item = new AddrItem(cursor.getInt(cursor.getColumnIndex("ADDR_ID")),
                                cursor.getInt(cursor.getColumnIndex("ADDR_CODE")),
                                cursor.getString(cursor.getColumnIndex("ADDR_NAME")),
                                cursor.getInt(cursor.getColumnIndex("PARENT_ID")));
                        addrItems.add(item);
                    } while (cursor.moveToNext());
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
                if (db != null) {
                    db.close();
                }
            }

        }
        // LogUtil.d("com.njhn.weetmall", "查询数据库：" + addrItems.size() + " "
        // + addrItems.toString());
        return addrItems;
    }

    /**
     * 根据地区码查询对应的地区名称 如420000|420100|420111
     */
    public String getAddrName(String code) {
        if (TextUtils.isEmpty(code)) {
            return null;
        }
        StringBuffer stringBuffer = new StringBuffer();
        String addrCodes = "";
        // 分割编码
        if (code.contains("|")) {
            String[] codes = code.split("\\|");
            if (codes != null && codes.length > 0) {
                for (int i = 0; i < codes.length; i++) {
                    addrCodes += " ADDR_CODE = " + codes[i];
                    if (i != codes.length - 1) {
                        addrCodes += " or ";
                    }
                }
            }
        }

        Cursor cursor = null;
        synchronized (DB_LOCK) {
            try {
            	dbm.openDatabase();
        	 	db = dbm.getDatabase();
                if (db == null) {
                    return null;
                }
                String sql = "select * from ADDR where" + addrCodes;
                // LogUtil.d("com.njhn.weetmall", "sql = " + sql);
                cursor = db.rawQuery(sql, null);
                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        stringBuffer.append(cursor.getString(cursor.getColumnIndex("ADDR_NAME")));
                        stringBuffer.append(" ");
                    } while (cursor.moveToNext());
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
                if (db != null) {
                    db.close();
                }
            }
        }
        // LogUtil.d("com.njhn.weetmall", "stringBuffer.toString() = "
        // + stringBuffer.toString());
        return stringBuffer.toString();
    }

    /**
     * 根据地区ID查询对应的地区码 如420000|420100|420111
     *
     * @param addrId 地区ID
     * @return 地区码
     */
    public String getAddrCode(int addrId) {
        String addrCode = "";
        Cursor cursor = null;
        synchronized (DB_LOCK) {
            try {
            	dbm.openDatabase();
        	 	db = dbm.getDatabase();
                if (db == null) {
                    return null;
                }
                String sql = "select ADDR_CODE from ADDR where ADDR_ID=" + addrId;
                // LogUtil.d("com.njhn.weetmall", "sql = " + sql);
                cursor = db.rawQuery(sql, null);
                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        addrCode = cursor.getString(cursor.getColumnIndex("ADDR_CODE"));
                    } while (cursor.moveToNext());
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
                if (db != null) {
                    db.close();
                }
            }
        }
        return addrCode;
    }

    /**
     * 根据地区名称查询对应的addID
     *
     * @param addrName 地区名称
     * @return 地区码
     */
    public String getAddrName(int addrID) {
        Cursor cursor = null;
        String addrName = null;
        synchronized (DB_LOCK) {
            try {
            	dbm.openDatabase();
        	 	db = dbm.getDatabase();
                if (db == null) {
                    return null;
                }
                String sql = "select ADDR_NAME from ADDR where ADDR_ID='" + addrID + "'";
                // LogUtil.d("com.njhn.weetmall", "sql = " + sql);
                cursor = db.rawQuery(sql, null);
                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        addrName = cursor.getString(cursor.getColumnIndex("ADDR_NAME"));
                    } while (cursor.moveToNext());
                }

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
                if (db != null) {
                    db.close();
                }
            }
        }
        return addrName;
    }

    /**
     * 通过定位到的区域编码，获取parentId
     *
     * @param [areaCodeStr]-[定位到的区域编码] <br/>
     * @param [参数2]-[参数2说明]            <br/>
     */
    public int getParentId(String areaCodeStr) {
        Cursor cursor = null;
        int parentId = -1;
        synchronized (DB_LOCK) {
            try {
            	dbm.openDatabase();
        	 	db = dbm.getDatabase();
                if (db == null) {
                    return parentId;
                }
                String sql = "select * from ADDR where ADDR_CODE = " + areaCodeStr;
                cursor = db.rawQuery(sql, null);
                if (cursor != null && cursor.moveToFirst()) {
                    parentId = cursor.getInt(cursor.getColumnIndex("PARENT_ID"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
                if (db != null) {
                    db.close();
                }
            }

        }
        return parentId;
    }

    /**
     * 通过定位到的区域编码，获取parentId
     *
     * @param [areaCodeStr]-[定位到的区域编码] <br/>
     * @param [参数2]-[参数2说明]            <br/>
     */
    public int getAddrId(String areaCodeStr) {
        Cursor cursor = null;
        int parentId = -1;
        synchronized (DB_LOCK) {
            try {
            	dbm.openDatabase();
        	 	db = dbm.getDatabase();
                if (db == null) {
                    return parentId;
                }
                String sql = "select * from ADDR where ADDR_CODE = " + areaCodeStr;
                cursor = db.rawQuery(sql, null);
                if (cursor != null && cursor.moveToFirst()) {
                    parentId = cursor.getInt(cursor.getColumnIndex("ADDR_ID"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
                if (db != null) {
                    db.close();
                }
            }

        }
        return parentId;
    }

    /**
     * 通过定位到的区域编码，获取parentId
     *
     * @param [areaCodeStr]-[定位到的区域编码] <br/>
     * @param [参数2]-[参数2说明]            <br/>
     */
    public AddrItem getAddrItem(int addrID) {
        Cursor cursor = null;
        AddrItem addrItem = null;
        synchronized (DB_LOCK) {
            try {
            	dbm.openDatabase();
        	 	db = dbm.getDatabase();
                if (db == null) {
                    return addrItem;
                }
                String sql = "select * from ADDR where ADDR_ID = " + addrID;
                cursor = db.rawQuery(sql, null);
                if (cursor != null && cursor.moveToFirst()) {
                    addrItem = new AddrItem(cursor.getInt(cursor.getColumnIndex("ADDR_ID")),
                            cursor.getInt(cursor.getColumnIndex("ADDR_CODE")),
                            cursor.getString(cursor.getColumnIndex("ADDR_NAME")),
                            cursor.getInt(cursor.getColumnIndex("PARENT_ID")));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
                if (db != null) {
                    db.close();
                }
            }

        }
        return addrItem;
    }
	
}
